/**    
* @文件名: CommonServiceImpl.java  
* @包名 cn.com.gzheroli.core.common.service.impl  
* @描述: TODO(用一句话描述该文件做什么)  
* @作者 李长荣  lichang_20011@163.com   
* @日期 2016年10月17日 上午10:21:20  
* @version V1.0    
*/
package cn.com.gzheroli.core.common.service.impl;

import java.io.Serializable;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.jdbc.SQL;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.alibaba.fastjson.JSONObject;

import cn.com.gzheroli.bfw.pojo.base.TSOperation;
import cn.com.gzheroli.bfw.pojo.base.TSRoleFunction;
import cn.com.gzheroli.cache.SpringRedisCache;
import cn.com.gzheroli.core.common.dao.BaseDAO;
import cn.com.gzheroli.core.common.dao.jdbc.JdbcDao;
import cn.com.gzheroli.core.common.mapper.CommonMapper;
import cn.com.gzheroli.core.common.model.json.TreeGrid;
import cn.com.gzheroli.core.common.service.CommonService;
import cn.com.gzheroli.core.common.service.SystemService;
import cn.com.gzheroli.core.util.NameHandler;
import cn.com.gzheroli.core.util.MyBatisSqlUtils;
import cn.com.gzheroli.core.util.ReflectHelper;
import cn.com.gzheroli.core.util.oConvertUtils;
import cn.com.gzheroli.core.util.mybatis.SqlHelper;
import cn.com.gzheroli.core.util.mybatis.SqlMapper;
import cn.com.gzheroli.tag.core.DataGrid;
import cn.com.gzheroli.tag.vo.easyui.TreeGridModel;
import cn.com.gzheroli.util.TagUtil;

/**
 * @类名: CommonServiceImpl
 * @描述: TODO(这里用一句话描述这个类的作用)
 * @作者: 李长荣 lichang_20011@163.com
 * @日期: 2016年10月17日 上午10:21:20
 * 
 */
@Service("commonService")
public class CommonServiceImpl implements CommonService {
	private static final Logger logger = Logger.getLogger(CommonServiceImpl.class);

	/**
	 * 数据库类型
	 */
	public static final String DATABSE_TYPE_MYSQL = "mysql";
	public static final String DATABSE_TYPE_POSTGRE = "postgresql";
	public static final String DATABSE_TYPE_ORACLE = "oracle";
	public static final String DATABSE_TYPE_SQLSERVER = "sqlserver";
	/**
	 * 分页SQL
	 */
	public static final String MYSQL_SQL = "select * from ( {0}) sel_tab00 limit {1},{2}"; // mysql
	public static final String POSTGRE_SQL = "select * from ( {0}) sel_tab00 limit {2} offset {1}";// postgresql
	public static final String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}"; // oracle
	public static final String SQLSERVER_SQL = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}"; // sqlserver

	@Resource
	private CommonMapper commonMapper;
	@Resource
	private SystemService systemService;
	@Autowired
	// SQL 使用JdbcDao
	private JdbcDao jdbcDao;
	@Autowired
	private BaseDAO baseDao;
	@Autowired
	private SpringRedisCache springRedisCache;
	@Autowired
	SqlMapper sqlMapper;
	
	@Override
	public <T> T getEntity(Class<T> resultType, Serializable id) {
		String tableName = NameHandler.getTableName(resultType.getSimpleName());
		Class superClass = resultType.getSuperclass();
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		SQL sql = new SQL();
		if (!superClass.getSimpleName().equals("Object")) {
			String tableName2 = NameHandler.getTableName(superClass.getSimpleName());
			sql.SELECT("t2.*");
			sql.FROM(tableName2 + " t2 ");
			/*if (tableColumns.containsKey("ID")) {
				sql.WHERE("t1.id=t2.id");
			} else if (tableColumns.containsKey("ID_")) {
				sql.WHERE("t1.id_=t2.id");
			}*/
			String key=systemService.getTableColumn(tableName, "id",superClass);
			//String key=tableColumns.get("id")==null?"ID":tableColumns.get("id").toString();
			sql.WHERE("t1."+key+"=t2."+key);
		}

		sql.SELECT("t1.*");
		sql.FROM(tableName + " t1 ");
		//String key=tableColumns.get("id")==null?"ID":tableColumns.get("id").toString();
		String key=systemService.getTableColumn(tableName, "id",resultType);
		sql.WHERE("t1."+key+"=#{id}");
		Map para = new HashMap();
		List<Map<String, Object>> mapList;
		para.put("id", id);
		return sqlMapper.selectOne(sql.toString(), para, resultType);
	}

	@Override
	public <T> T findUniqueByProperty(Class<T> entityClass, String propertyName, Object value) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		//String columnName = NameHandler.getColumnName(propertyName);
		//columnName = tableColumns.get(propertyName)==null?columnName:tableColumns.get(propertyName).toString();
		String columnName=systemService.getTableColumn(tableName, propertyName,entityClass);
		SQL sql = new SQL();
		sql.SELECT("t1.*");
		Class superClass = entityClass.getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			String tableName2 = NameHandler.getTableName(superClass.getSimpleName());
			sql.SELECT("t2.*");
			sql.FROM(tableName2 + " t2 ");
			//String key=tableColumns.get("id")==null?"ID":tableColumns.get("id").toString();
			String key=systemService.getTableColumn(tableName, "id",superClass);
			sql.WHERE("t1."+key+"=t2."+key);
		}
		sql.FROM(tableName + " t1");
		if (value != null) {
			sql.WHERE("t1." + columnName + "=#{" + propertyName + "}");
		} else {
			sql.WHERE("t1." + columnName + " is null");
		}
		Map para = new HashMap();
		List<Map<String, Object>> mapList;
		para.put(propertyName, value);
		return sqlMapper.selectOne(sql.toString(), para, entityClass);
	}

	public <T> T findUnique(Class<T> entityClass, Map<String, Object> para) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		// List<String> tableColumns=TableAndColumns.allTableColumns.get(tableName);
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		// String sql="select * from "+tableName+" where 1=1 ";
		SQL sql = new SQL();
		sql.SELECT("t1.*");
		sql.FROM(tableName + " t1");
		Class superClass = entityClass.getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			String tableName2 = NameHandler.getTableName(superClass.getSimpleName());
			sql.SELECT("t2.*");
			sql.FROM(tableName2 + " t2 ");
			//String key=tableColumns.get("id")==null?"ID":tableColumns.get("id").toString();
			String key=systemService.getTableColumn(tableName2, "id",entityClass);
			sql.WHERE("t1."+key+"=t2."+key);
		}
		for (Map.Entry<String, Object> entry : para.entrySet()) {
			// System.out.println("key= " + entry.getKey() + " and value= " +
			// entry.getValue());
			String propertyName = entry.getKey();
			String columnName=systemService.getTableColumn(tableName, propertyName,entityClass);
			//String columnName = NameHandler.getColumnName(propertyName);
			//columnName = tableColumns.get(propertyName)==null?columnName:tableColumns.get(propertyName).toString();
			/*if (tableColumns.contains(columnName)) {
				columnName = columnName;
			} else if (tableColumns.contains(columnName + "_")) {
				columnName = columnName + "_";
			}*/
			if (para.get(propertyName) != null) {
				sql.WHERE("t1." + columnName + "=#{" + propertyName + "}");
			} else {
				sql.WHERE("t1." + columnName + " is null");
			}
		}

		List<Map<String, Object>> mapList;
		return sqlMapper.selectOne(sql.toString(), para, entityClass);
	}

	@Override
	public <T> List<T> findByProperty(Class<T> entityClass, String propertyName, Object value) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		//String columnName = NameHandler.getColumnName(propertyName);
		//columnName = tableColumns.get(propertyName)==null?columnName:tableColumns.get(propertyName).toString();
		String columnName=systemService.getTableColumn(tableName, propertyName,entityClass);
		SQL sql = new SQL();
		sql.SELECT("t1.*");
		Class superClass = entityClass.getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			String tableName2 = NameHandler.getTableName(superClass.getSimpleName());
			sql.SELECT("t2.*");
			sql.FROM(tableName2 + " t2 ");
			//String key=tableColumns.get("id")==null?"ID":tableColumns.get("id").toString();
			String key=systemService.getTableColumn(tableName2, "id",superClass);
			sql.WHERE("t1."+key+"=t2."+key);
		}
		sql.FROM(tableName + " t1");
		sql.WHERE("t1." + columnName + "=#{" + propertyName + "}");
		Map para = new HashMap();
		List<Map<String, Object>> mapList;
		para.put(propertyName, value);
		return sqlMapper.selectList(sql.toString(), para, entityClass);
	}

	@Override
	public <T> T get(Class<T> clazz, final Serializable id) {
		String tableName = NameHandler.getTableName(clazz.getSimpleName());
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		SQL sql = new SQL();
		sql.SELECT("t1.*");
		Class superClass = clazz.getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			String tableName2 = NameHandler.getTableName(superClass.getSimpleName());
			sql.SELECT("t2.*");
			sql.FROM(tableName2 + " t2 ");
			//String key=tableColumns.get("id")==null?"ID":tableColumns.get("id").toString();
			String key=systemService.getTableColumn(tableName2, "id",clazz);
			sql.WHERE("t1."+key+"=t2."+key);
		}
		sql.FROM(tableName + " t1");
		String key=systemService.getTableColumn(tableName, "id",clazz);
		sql.WHERE("t1."+key+"=#{id}");
		

		Map para = new HashMap();
		List<Map<String, Object>> mapList;
		para.put("id", id);
		return sqlMapper.selectOne(sql.toString(), para, clazz);
	}

	@Override
	public void updateEntitie(Object pojo, Class<?>... args) {
		String tableName = NameHandler.getTableName(pojo.getClass().getSimpleName());
		List<String> allTables = springRedisCache.getCache("allUserTable", List.class);
		if (allTables==null||!allTables.contains(tableName)) {
			systemService.getInitAllTables(tableName);
			allTables = springRedisCache.getCache("allUserTable", List.class);
		}
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,pojo.getClass());
		if (tableColumns == null) {
			return;
		}
		if (args == null || args.length == 0) {
			Map<String, Object> paraMap = MyBatisSqlUtils.buildUpdateSql(pojo, tableColumns);
			commonMapper.updateRecord(paraMap);
			Class superClass = pojo.getClass().getSuperclass();
			if (!superClass.getSimpleName().equals("Object")) {
				tableName = new NameHandler().getTableName(superClass.getSimpleName());
				allTables = springRedisCache.getCache("allUserTable", List.class);
				if (allTables==null||!allTables.contains(tableName)) {
					systemService.getInitAllTables(tableName);
					allTables = springRedisCache.getCache("allUserTable", List.class);
				}
				if (allTables.contains(tableName)) {
					tableColumns = systemService.getHeroTableColumns(tableName,superClass);
					Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildUpdateSql(pojo, tableColumns, superClass);
					commonMapper.updateRecord(paraMapSupper);
				}
			}
		} else if (args != null && args.length == 1) {
			tableName = NameHandler.getTableName(args[0].getSimpleName());
			allTables = springRedisCache.getCache("allUserTable", List.class);
			if (allTables==null||!allTables.contains(tableName)) {
				systemService.getInitAllTables(tableName);
				allTables = springRedisCache.getCache("allUserTable", List.class);
			}
			if (allTables.contains(tableName)) {
				tableColumns = systemService.getHeroTableColumns(tableName,args[0]);
				Map<String, Object> paraMap = MyBatisSqlUtils.buildUpdateSql(pojo, tableColumns, args[0]);
				commonMapper.updateRecord(paraMap);
			}
			Class superClass = pojo.getClass().getSuperclass();
			if (!superClass.getSimpleName().equals("Object")) {
				tableName = NameHandler.getTableName(superClass.getSimpleName());
				allTables = springRedisCache.getCache("allUserTable", List.class);
				if (allTables==null||!allTables.contains(tableName)) {
					systemService.getInitAllTables(tableName);
					allTables = springRedisCache.getCache("allUserTable", List.class);
				}
				if (allTables.contains(tableName)) {
					tableColumns = systemService.getHeroTableColumns(tableName,superClass);
					Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildUpdateSql(pojo, tableColumns, superClass);
					commonMapper.updateRecord(paraMapSupper);
				}
			}
		}

	}

	@Override
	public void save(Object entity) {
		String tableName = NameHandler.getTableName(entity.getClass().getSimpleName());
		List<String> allTables = springRedisCache.getCache("allUserTable", List.class);
		if (allTables==null||!allTables.contains(tableName)) {
			systemService.getInitAllTables(tableName);
			allTables = springRedisCache.getCache("allUserTable", List.class);
		}
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,entity.getClass());
		Map<String, Object> paraMap = MyBatisSqlUtils.buildInsertSql(entity, tableColumns);
		commonMapper.addRecord(paraMap);
		Class superClass = entity.getClass().getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			tableName = new NameHandler().getTableName(superClass.getSimpleName());
			if (allTables==null||!allTables.contains(tableName)) {
				systemService.getInitAllTables(tableName);
				allTables = springRedisCache.getCache("allUserTable", List.class);
			}
			if (allTables.contains(tableName)) {
				tableColumns = systemService.getHeroTableColumns(tableName,superClass);
				Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildInsertSql(entity, tableColumns, superClass);
				if (paraMapSupper.containsKey("id") || paraMapSupper.containsKey("ID")
						|| paraMapSupper.containsKey("ID_")) {
					Long size = this.getCountForJdbc(
							"select count(1) from " + tableName + " where ID='" + paraMapSupper.get("id") + "'");
					if (size == 0) {
						commonMapper.addRecord(paraMapSupper);
					}
				}
			}

		}
	}

	@Override
	public void save(Object entity, String suffix) {
		String tableName = NameHandler.getTableName(entity.getClass().getSimpleName());
		tableName = tableName + "_" + suffix.toUpperCase();
		List<String> allTables = springRedisCache.getCache("allUserTable", List.class);
		Map<String, Object> paraMap = MyBatisSqlUtils.buildInsertSql(entity, tableName);
		commonMapper.addRecord(paraMap);
		Class superClass = entity.getClass().getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			tableName = NameHandler.getTableName(superClass.getSimpleName());
			tableName = tableName + "_" + suffix.toUpperCase();
			if (allTables==null||!allTables.contains(tableName)) {
				allTables = systemService.getAllTables(tableName);
			}
			if (allTables.contains(tableName)) {
				Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildInsertSql(entity,
						superClass.getSimpleName() + "_" + suffix.toUpperCase());
				if (paraMapSupper.containsKey("id") || paraMapSupper.containsKey("ID")
						|| paraMapSupper.containsKey("ID_")) {
					Long size = this.getCountForJdbc(
							"select count(1) from " + tableName + " where ID='" + paraMapSupper.get("id") + "'");
					if (size == 0) {
						commonMapper.addRecord(paraMapSupper);
					}
				}
			}

		}
	}

	@Override
	public void saveField(Map<String, Object> paraMap, Class clazz) {
		String tableName = NameHandler.getTableName(clazz.getSimpleName());
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		paraMap.remove("sql");
		SQL sql = new SQL();
		sql.INSERT_INTO(tableName);
		for (String key : paraMap.keySet()) {
			//String columnName = NameHandler.getColumnName(key);
			//columnName = tableColumns.get(key)==null?columnName:tableColumns.get(key).toString();
			String columnName=systemService.getTableColumn(tableName, key,clazz);
			sql.VALUES(columnName, "#{" + key + "}");
			/*if (tableColumns.contains(columnName)) {
				sql.VALUES(columnName, "#{" + key + "}");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.VALUES(columnName + "_", "#{" + key + "}");
			} else {
				continue;
			}*/
		}

		paraMap.put("sql", sql.toString());
		commonMapper.addRecord(paraMap);
	}

	@Override
	public void saveMap(Map<String, Object> paraMap, String tableName,Class clazz) {
		if (tableName.indexOf("_") == -1) {
			tableName = NameHandler.getTableName(tableName);
		} else {
			tableName = tableName.toUpperCase();
		}
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		paraMap.remove("sql");
		SQL sql = new SQL();
		sql.INSERT_INTO(tableName);
		for (String key : paraMap.keySet()) {
			//String columnName = NameHandler.getColumnName(key);
			//columnName = tableColumns.get(key)==null?columnName:tableColumns.get(key).toString();
			String columnName=systemService.getTableColumn(tableName, key,clazz);
			/*if (tableColumns.contains(columnName)) {
				sql.VALUES(columnName, "#{" + key + "}");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.VALUES(columnName + "_", "#{" + key + "}");
			} else {
				continue;
			}*/
			sql.VALUES(columnName, "#{" + key + "}");
		}

		paraMap.put("sql", sql.toString());
		commonMapper.addRecord(paraMap);
	}

	@Override
	public void updateMap(Map<String, Object> paraMap, Map<String, Object> whereMap, String tableName,Class clazz) {
		if (tableName.indexOf("_") == -1) {
			tableName = NameHandler.getTableName(tableName);
		} else {
			tableName = tableName.toUpperCase();
		}
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		paraMap.remove("sql");
		SQL sql = new SQL();
		sql.UPDATE(tableName);
		for (String key : paraMap.keySet()) {
			//String columnName = NameHandler.getColumnName(key);
			String columnName=systemService.getTableColumn(tableName, key,clazz);
			String sKey = "=#{" + key + "}";
			if (paraMap.get(key) instanceof Date) {
				sKey = "=#{" + key + ",jdbcType=TIMESTAMP}";
			}
			//columnName = tableColumns.get(key)==null?columnName:tableColumns.get(key).toString();
			sql.SET(columnName + sKey);
			/*if (tableColumns.contains(columnName)) {
				sql.SET(columnName + sKey);
			} else if (tableColumns.contains(columnName + "_")) {
				sql.SET(columnName + "_" + sKey);
			} else {
				continue;
			}*/
		}
		for (String key : whereMap.keySet()) {
			String columnName=systemService.getTableColumn(tableName, key,clazz);
			//String columnName = NameHandler.getColumnName(key);
			//columnName = tableColumns.get(key)==null?columnName:tableColumns.get(key).toString();
			sql.WHERE(columnName + "=#{" + key + "}");
			/*if (tableColumns.contains(columnName)) {
				sql.WHERE(columnName + "=#{" + key + "}");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.WHERE(columnName + "_" + "=#{" + key + "}");
			} else {
				continue;
			}*/
		}
		if (null == whereMap || whereMap.isEmpty()) {
			/*if (tableColumns.contains("ID")) {
				sql.WHERE("id=#{id}");
			} else if (tableColumns.contains("ID_")) {
				sql.WHERE("id_=#{id}");
			}*/
			//String key=tableColumns.get("id")==null?"ID":tableColumns.get("id").toString();
			String key=systemService.getTableColumn(tableName, "id",clazz);
			sql.WHERE("t1."+key+"=t2."+key);
		}
		paraMap.putAll(whereMap);
		paraMap.put("sql", sql.toString());
		commonMapper.updateRecord(paraMap);
	}

	@Override
	public void update(Object entity) {
		String tableName = NameHandler.getTableName(entity.getClass().getSimpleName());
		List<String> allTables = springRedisCache.getCache("allUserTable", List.class);
		if (allTables==null||!allTables.contains(tableName)) {
			systemService.getInitAllTables(tableName);
			allTables = springRedisCache.getCache("allUserTable", List.class);
		}
		Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName,entity.getClass());
		Map<String, Object> paraMap = MyBatisSqlUtils.buildUpdateSql(entity, tableColumns);
		commonMapper.updateRecord(paraMap);
		Class superClass = entity.getClass().getSuperclass();
		if (!superClass.getSimpleName().equals("Object")) {
			tableName = NameHandler.getTableName(superClass.getSimpleName());
			allTables = springRedisCache.getCache("allUserTable", List.class);
			if (allTables==null||!allTables.contains(tableName)) {
				systemService.getInitAllTables(tableName);
				allTables = springRedisCache.getCache("allUserTable", List.class);
			}
			if (allTables.contains(tableName)) {
				tableColumns = systemService.getHeroTableColumns(tableName,superClass);
				Map<String, Object> paraMapSupper = MyBatisSqlUtils.buildUpdateSql(entity, tableColumns, superClass);
				commonMapper.updateRecord(paraMapSupper);
			}
		}
	}

	@Override
	public  void update(Object entity,String suffix) {
		String tableName = NameHandler.getTableName(entity.getClass().getSimpleName());
		tableName=tableName+"_"+suffix.toUpperCase();
		List<String> allTables=springRedisCache.getCache("allUserTable", List.class);
		Map<String,Object> paraMap=MyBatisSqlUtils.buildUpdateSql(entity, tableName,null);
		commonMapper.updateRecord(paraMap);
		Class superClass=entity.getClass().getSuperclass();
		if(!superClass.getSimpleName().equals("Object")){
			tableName = NameHandler.getTableName(superClass.getSimpleName());
			tableName=tableName+"_"+suffix.toUpperCase();
			if(!allTables.contains(tableName)||allTables==null){
				allTables=systemService.getAllTables(tableName);
			}
			if(allTables.contains(tableName)){
				Map<String,Object> paraMapSupper=MyBatisSqlUtils.buildUpdateSql(entity,tableName,null);
				commonMapper.updateRecord(paraMapSupper);
			}
		}
	}

	@Override
	public void updateField(Map<String, Object> paraMap, Map<String, Object> whereMap, Class clazz) {
		String tableName = NameHandler.getTableName(clazz.getSimpleName());
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		paraMap.remove("sql");
		SQL sql = new SQL();
		sql.UPDATE(tableName);
		for (String key : paraMap.keySet()) {
			//String columnName = NameHandler.getColumnName(key);
			//columnName = tableColumns.get(key)==null?columnName:tableColumns.get(key).toString();
			String columnName=systemService.getTableColumn(tableName, key,clazz);
			String sKey = "=#{" + key + "}";
			if (paraMap.get(key) instanceof Date) {
				sKey = "=#{" + key + ",jdbcType=TIMESTAMP}";
			}
			sql.SET(columnName + sKey);
			/*if (tableColumns.contains(columnName)) {
				sql.SET(columnName + sKey);
			} else if (tableColumns.contains(columnName + "_")) {
				sql.SET(columnName + "_" + sKey);
			} else {
				continue;
			}*/
		}
		for (String key : whereMap.keySet()) {
			//String columnName = NameHandler.getColumnName(key);
			/*if (tableColumns.contains(columnName)) {
				sql.WHERE(columnName + "=#{" + key + "}");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.WHERE(columnName + "_" + "=#{" + key + "}");
			} else {
				continue;
			}*/
			//columnName = tableColumns.get(key)==null?columnName:tableColumns.get(key).toString();
			String columnName=systemService.getTableColumn(tableName, key,clazz);
			sql.WHERE(columnName + "=#{" + key + "}");
		}
		if (null == whereMap || whereMap.isEmpty()) {
			/*if (tableColumns.contains("ID")) {
				sql.WHERE("id=#{id}");
			} else if (tableColumns.contains("ID_")) {
				sql.WHERE("id_=#{id}");
			}*/
			//String key=tableColumns.get("id")==null?"ID":tableColumns.get("id").toString();
			String key=systemService.getTableColumn(tableName, "id",clazz);
			sql.WHERE("t1."+key+"=t2."+key);
		}
		paraMap.putAll(whereMap);
		paraMap.put("sql", sql.toString());
		commonMapper.updateRecord(paraMap);
	}

	@Override
	public Long getCountForJdbc(String sql) {
		Map para = new HashMap();
		para.put("sql", sql);
		return commonMapper.selectCountBySql(para);
	}

	@Override
	public Long getCountForJdbc(String sql, Map params) {
		Map para = new HashMap();
		para.put("sql", sql);
		para.putAll(params);
		return commonMapper.selectCountBySql(para);
	}

	/**
	 * 
	 * @Title: selectListByPageSql @Description:分页获取对象数据 @param @param
	 * sql @param @param entityClass @param @param para oracle:firstResult
	 * 起始行数,maxResults 截至记录行数 mysql:beginRow 起始行数,pageSize 每页显示记录数 @param page
	 * 当前页数 @param limit 每页显示条数 @param @return 设定文件 @return List<T> 返回类型 @throws
	 */
	public <T> List<T> findForJdbcPage(String sql, Class<T> entityClass, Map<String, Object> para, int page,
			int limit) {
		int beginNum = (page - 1) * limit;
		String[] sqlParam = new String[3];
		sqlParam[0] = sql;
		sqlParam[1] = beginNum + "";
		sqlParam[2] = limit + "";
		int beginIndex = (page - 1) * limit;
		int endIndex = beginIndex + limit;
		String dataBaseType = sqlMapper.getDatabaseType();
		if (dataBaseType.indexOf(DATABSE_TYPE_MYSQL) != -1) {
			sql = MessageFormat.format(MYSQL_SQL, sqlParam);
		} else if (dataBaseType.indexOf(DATABSE_TYPE_POSTGRE) != -1) {
			sql = MessageFormat.format(POSTGRE_SQL, sqlParam);
		} else {
			sqlParam[2] = Integer.toString(beginIndex);
			sqlParam[1] = Integer.toString(endIndex);
			if (dataBaseType.indexOf(DATABSE_TYPE_ORACLE) != -1) {
				sql = MessageFormat.format(ORACLE_SQL, sqlParam);
			} else if (dataBaseType.indexOf(DATABSE_TYPE_SQLSERVER) != -1) {
				sqlParam[0] = sql.substring(sqlMapper.getAfterSelectInsertPoint(sql));
				sql = MessageFormat.format(SQLSERVER_SQL, sqlParam);
			}
		}
		return sqlMapper.selectList(sql, para, entityClass);
	}

	/**
	 * 
	 * @Title: selectListByPageForMap @Description:
	 * 分页获取记录数，返回list<map> @param @param sql @param @param para 参数
	 * oracle:firstResult 起始行数,maxResults 截至记录行数 mysql:beginRow 起始行数,pageSize
	 * 每页显示记录数 @param @return 设定文件 @return List<Map<String,Object>> 返回类型 @throws
	 */
	public List<Map<String, Object>> findForJdbcMap(String sql, Map<String, Object> para) {
		List<Map<String, Object>> mapList;
		if (null == para) {
			para = new HashMap();
		}
		para.put("sql", sql.toString());
		mapList = commonMapper.selectListByPageSql(para);
		return mapList;
	}

	public <T> List<T> findByQueryString(String sql, Class<T> entityClass, Map<String, Object> para) {
		List<Map<String, Object>> mapList;
		return sqlMapper.selectList(sql.toString(), para, entityClass);
	}

	public <T> List<T> findByQueryString(Class<T> entityClass, Map<String, Object> para) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		if (null == para) {
			para = new HashMap();
		}
		para.remove("sql");
		SQL sql = new SQL();
		sql.SELECT("*");
		sql.FROM(tableName);
		for (Map.Entry<String, Object> entry : para.entrySet()) {
			String propertyName = entry.getKey();
			if (!propertyName.trim().equals("orderBy")) {
				//String columnName = NameHandler.getColumnName(propertyName);
				//columnName = tableColumns.get(propertyName)==null?columnName:tableColumns.get(propertyName).toString();
				String columnName=systemService.getTableColumn(tableName, propertyName,entityClass);
				sql.WHERE(columnName + "=#{" + propertyName + "}");
				/*if (tableColumns.contains(columnName)) {
					sql.WHERE(columnName + "=#{" + propertyName + "}");
				} else if (tableColumns.contains(columnName + "_")) {
					sql.WHERE(columnName + "_" + "=#{" + propertyName + "}");
				} else {
					continue;
				}*/
			}
		}
		if (para.get("orderBy") != null) {
			String orderBy = (String) para.get("orderBy");
			orderBy = orderBy.trim();
			String order[] = orderBy.split(" ");
			sql.ORDER_BY(" " + order[0] + " " + order[order.length - 1]);
		}

		List<Map<String, Object>> mapList;
		return sqlMapper.selectList(sql.toString(), para, entityClass);
	}

	public <T> Long findByQueryStringCount(Class<T> entityClass, Map<String, Object> para) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		if (null == para) {
			para = new HashMap();
		}
		para.remove("sql");
		SQL sql = new SQL();
		sql.SELECT("count(*)");
		sql.FROM(tableName);
		for (Map.Entry<String, Object> entry : para.entrySet()) {
			String propertyName = entry.getKey();
			if (!propertyName.trim().equals("orderBy")) {
				//String columnName = NameHandler.getColumnName(propertyName);
				//columnName = tableColumns.get(propertyName)==null?columnName:tableColumns.get(propertyName).toString();
				String columnName=systemService.getTableColumn(tableName, propertyName,entityClass);
				sql.WHERE(columnName + "=#{" + propertyName + "}");
				/*if (tableColumns.contains(columnName)) {
					sql.WHERE(columnName + "=#{" + propertyName + "}");
				} else if (tableColumns.contains(columnName + "_")) {
					sql.WHERE(columnName + "_" + "=#{" + propertyName + "}");
				} else {
					continue;
				}*/
			}
		}

		para.put("sql", sql.toString());
		// para.put(propertyName, value);
		return commonMapper.selectCountBySql(para);
	}

	@Override
	public void delete(Class obj, Object id) {
		String tableName = NameHandler.getTableName(obj.getSimpleName());
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		SQL sql = new SQL();
		sql.DELETE_FROM(tableName);
		/*if (tableColumns.contains("ID")) {
			sql.WHERE("id=#{id}");
		} else if (tableColumns.contains("ID_")) {
			sql.WHERE("id_=#{id}");
		}*/
		//String key=tableColumns.get("id")==null?"ID":tableColumns.get("id").toString();
		String key=systemService.getTableColumn(tableName, "id",obj);
		sql.WHERE(key+"=#{id}");
		Map paras = new HashMap();
		paras.put("id", id);
		paras.put("sql", sql.toString());
		commonMapper.deleteRecord(paras);
	}

	public List<String> getStringListBySql(Class obj, String selColName, Map<String, Object> para, String orderCol,
			String orderBy) {
		String tableName = NameHandler.getTableName(obj.getSimpleName());
		// List<String> tableColumns=TableAndColumns.allTableColumns.get(tableName);
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		//String selColumn = NameHandler.getColumnName(selColName);
		//selColumn = tableColumns.get(selColName)==null?selColumn:tableColumns.get(selColName).toString();
		String selColumn=systemService.getTableColumn(tableName, selColName,obj);
		if (null == para) {
			para = new HashMap();
		}
		para.remove("sql");
		SQL sql = new SQL();
		/*if (tableColumns.contains(selColumn)) {
			selColumn = selColumn;
		} else if (tableColumns.contains(selColumn + "_")) {
			selColumn = selColumn + "_";
		}*/
		sql.SELECT(selColumn);
		sql.FROM(tableName);
		for (Map.Entry<String, Object> entry : para.entrySet()) {
			String propertyName = entry.getKey();
			//String columnName = NameHandler.getColumnName(propertyName);
			//columnName = tableColumns.get(propertyName)==null?columnName:tableColumns.get(propertyName).toString();
			String columnName=systemService.getTableColumn(tableName, propertyName,obj);
			sql.WHERE(columnName + "=#{" + propertyName + "}");
			/*if (tableColumns.contains(columnName)) {
				sql.WHERE(columnName + "=#{" + propertyName + "}");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.WHERE(columnName + "_" + "=#{" + propertyName + "}");
			} else {
				continue;
			}*/
		}
		if (orderCol != null && !orderCol.trim().equals("")) {
			//String orderColumn = NameHandler.getColumnName(orderCol);
			//orderColumn = tableColumns.get(orderCol)==null?orderColumn:tableColumns.get(orderCol).toString();
			String orderColumn=systemService.getTableColumn(tableName, orderCol,obj);
			/*if (tableColumns.contains(orderColumn)) {
				orderColumn = orderColumn;
			} else if (tableColumns.contains(orderColumn + "_")) {
				orderColumn = orderColumn + "_";
			}*/
			if (orderBy == null || orderBy.trim().equals("")) {
				orderBy = " asc";
			}
			sql.ORDER_BY(orderColumn + " " + orderBy);
		}
		para.put("sql", sql.toString());
		return commonMapper.getStringListBySql(para);
	}

	public List<String> getStringListBySql(String sql, Map<String, Object> para) {
		if (null == para) {
			para = new HashMap();
		}
		para.remove("sql");
		para.put("sql", sql);
		return commonMapper.getStringListBySql(para);
	}

	@Override
	public void delete(Class obj, Map<String, Object> para) {
		String tableName = NameHandler.getTableName(obj.getSimpleName());
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		if (null == para) {
			para = new HashMap();
		}
		para.remove("sql");
		SQL sql = new SQL();
		sql.DELETE_FROM(tableName);
		for (Map.Entry<String, Object> entry : para.entrySet()) {
			String propertyName = entry.getKey();
			//String columnName = NameHandler.getColumnName(propertyName);
			//columnName = tableColumns.get(propertyName)==null?columnName:tableColumns.get(propertyName).toString();
			String columnName=systemService.getTableColumn(tableName, propertyName,obj);
			sql.WHERE(columnName + "=#{" + propertyName + "}");
			/*if (tableColumns.contains(columnName)) {
				sql.WHERE(columnName + "=#{" + propertyName + "}");
			} else if (tableColumns.contains(columnName + "_")) {
				sql.WHERE(columnName + "_" + "=#{" + propertyName + "}");
			} else {
				continue;
			}*/
		}

		List<Map<String, Object>> mapList;
		para.put("sql", sql.toString());
		commonMapper.deleteRecord(para);
	}

	@Override
	public void delete(Class obj, String propertyName, Object value) {
		String tableName = NameHandler.getTableName(obj.getSimpleName());
		//Map<String,Object> tableColumns = systemService.getHeroTableColumns(tableName);
		//String columnName = NameHandler.getColumnName(propertyName);
		//columnName = tableColumns.get(propertyName)==null?columnName:tableColumns.get(propertyName).toString();
		/*if (tableColumns.contains(columnName)) {
			columnName = columnName;
		} else if (tableColumns.contains(columnName + "_")) {
			columnName = columnName + "_";
		}*/
		String columnName=systemService.getTableColumn(tableName, propertyName,obj);
		SQL sql = new SQL();
		sql.DELETE_FROM(tableName);
		sql.WHERE(columnName + "=#{" + propertyName + "}");
		Map paras = new HashMap();
		paras.put(propertyName, value);
		paras.put("sql", sql);
		commonMapper.deleteRecord(paras);
	}

	@Override
	public void deleteBySql(String sql, Map para) {
		if (null == para) {
			para = new HashMap();
		}
		para.put("sql", sql);
		commonMapper.deleteRecord(para);
	}

	@Override
	public Integer executeSql(String sql, List<Object> param) {
		return jdbcDao.executeSql(sql, param);
	}

	@Override
	public Integer executeSql(String sql, Object... param) {
		return jdbcDao.executeSql(sql, param);
	}

	@Override
	public Integer executeSql(String sql, Map<String, Object> param) {
		return jdbcDao.executeSql(sql, param);
	}

	@Override
	public Integer updateMapper(String sql, Map<String, Object> param) {
		return sqlMapper.update(sql, param);
	}

	@Override
	public Integer insertMapper(String sql, Map<String, Object> param) {
		return sqlMapper.insert(sql, param);
	}

	@Override
	public Object executeSqlReturnKey(String sql, Map<String, Object> param) {
		return jdbcDao.executeSqlReturnKey(sql, param);
	}

	@Override
	public <T> List<T> loadAll(Class<T> entityClass) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		String sql = "select * from " + tableName;
		Map paras = new HashMap();
		return sqlMapper.selectList(sql.toString(), entityClass);
	}

	@Override
	public int executeSQL(String sql) {
		return jdbcDao.executeSql(sql);
	}

	@Override
	public DataGrid getDataGridReturn(DataGrid dataGrid, Class clazz, String sql, Map para, boolean isOffset) {
		if (null == para) {
			para = new HashMap();
		}
		int page = dataGrid.getPage();
		int rows = dataGrid.getRows();
		para.put("sql", "select count(*) from (" + sql + ")");
		int total = commonMapper.selectCountBySql(para).intValue();
		List<Map<String, Object>> mapList;
		if (isOffset) {
			sql = jdbcDao.createPageSql(sql, page, rows);
		}
		para.remove("sql");
		List results = sqlMapper.selectList(sql.toString(), para, clazz);
		dataGrid.setTotal(total);
		dataGrid.setResults(results);
		return dataGrid;
	}

	public void batchInsert(Class entityClass, List list) {
		String tableName = NameHandler.getTableName(entityClass.getSimpleName());
		String sql = "select * from " + tableName;
		Map paras = new HashMap();
		String statementName = CommonMapper.class.getName() + ".addRecord";// CommonMapper.class.getPackage().getName()
		baseDao.batchInsert(statementName, list);
	}

	@Override
	public List<Map<String, Object>> findForJdbc(String sql, Map para) {
		if (null == para) {
			para = new HashMap();
		}
		para.put("sql", sql);
		return commonMapper.selectListBySql(para);
	}

	@Override
	public Map<String, Object> findOneForJdbc(String sql, Map para) {
		if (null == para) {
			para = new HashMap();
		}
		para.put("sql", sql);
		List<Map<String, Object>> list = commonMapper.selectListBySql(para);
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		} else {
			return null;
		}
	}

	@Override
	public <T> T findOneObjForJdbc(String sql, Map para, Class<T> clazz) {
		return sqlMapper.selectOne(sql.toString(), para, clazz);
	}

	/**
	 * 构建树形数据表
	 */
	public List<TreeGrid> treegrid(List all, TreeGridModel treeGridModel) {
		List<TreeGrid> treegrid = new ArrayList<TreeGrid>();
		for (Object obj : all) {
			ReflectHelper reflectHelper = new ReflectHelper(obj);
			TreeGrid tg = new TreeGrid();
			String id = oConvertUtils.getString(reflectHelper.getMethodValue(treeGridModel.getIdField()));
			String src = oConvertUtils.getString(reflectHelper.getMethodValue(treeGridModel.getSrc()));
			String text = oConvertUtils.getString(reflectHelper.getMethodValue(treeGridModel.getTextField()));
			if (StringUtils.isNotEmpty(treeGridModel.getOrder())) {
				String order = oConvertUtils.getString(reflectHelper.getMethodValue(treeGridModel.getOrder()));
				tg.setOrder(order);
			}
			tg.setId(id);
			if (treeGridModel.getIcon() != null) {
				String iconpath = TagUtil.fieldNametoValues(treeGridModel.getIcon(), obj).toString();
				if (iconpath != null) {
					tg.setCode(iconpath);
				} else {
					tg.setCode("");
				}
			}
			tg.setSrc(src);
			tg.setText(text);
			if (treeGridModel.getTreeType() != null) {
				if (treeGridModel.getTreeType().equals("function")) {
					Map attributes = new HashMap();
					attributes.put("isFront", "1");
					JSONObject extendObj = new JSONObject();
					extendObj.put("isFront", "1");
					// tg.setExtendObj(extendObj.toString());
					tg.setExtendObj(extendObj);
					tg.setAttributes(attributes);

				}
			}
			if (treeGridModel.getParentId() != null) {
				Object pid = TagUtil.fieldNametoValues(treeGridModel.getParentId(), obj);
				if (pid != null) {
					tg.setParentId(pid.toString());
				} else {
					tg.setParentId("");
				}
			}
			if (treeGridModel.getParentText() != null) {
				Object ptext = TagUtil.fieldNametoValues(treeGridModel.getTextField(), obj);
				if (ptext != null) {
					tg.setParentText(ptext.toString());
				} else {
					tg.setParentText("");
				}

			}
			List childList = (List) reflectHelper.getMethodValue(treeGridModel.getChildList());

			if (childList != null && childList.size() > 0) {
				tg.setState("closed");
			}
			if (treeGridModel.getRoleid() != null) {
				String[] opStrings = {};
				// List<TSRoleFunction> roleFunctions = findByProperty(TSRoleFunction.class,
				// "functionId", id);
				String sql = "select * from T_S_ROLE_FUNCTION rf where rf.FUNCTION_ID=#{functionId}";
				Map para = new HashMap();
				para.put("functionId", id);
				List<TSRoleFunction> roleFunctions = sqlMapper.selectList(sql, para, TSRoleFunction.class);
				if (roleFunctions.size() > 0) {
					for (TSRoleFunction tRoleFunction : roleFunctions) {
						TSRoleFunction roleFunction = tRoleFunction;
						if (roleFunction.getRoleId().toString().equals(treeGridModel.getRoleid())) {
							String bbString = roleFunction.getOperation();
							if (bbString != null) {
								opStrings = bbString.split(",");
								break;
							}
						}
					}
				}
				// List<TSOperation> operateions = findByProperty(TSOperation.class,
				// "functionId", id);
				sql = "select * from T_S_OPERATION o where o.FUNCTION_ID=#{functionId}";
				Map opPara = new HashMap();
				opPara.put("functionId", id);
				List<TSOperation> operateions = sqlMapper.selectList(sql, opPara, TSOperation.class);
				StringBuffer attributes = new StringBuffer();
				if (operateions.size() > 0) {
					for (TSOperation tOperation : operateions) {
						if (opStrings.length < 1) {
							attributes.append("<input type=checkbox name=operatons value=" + tOperation.getId() + "_"
									+ id + ">" + tOperation.getOperationName());
						} else {
							StringBuffer sb = new StringBuffer();
							sb.append("<input type=checkbox name=operatons");
							for (int i = 0; i < opStrings.length; i++) {
								if (opStrings[i].equals(tOperation.getId().toString())) {
									sb.append(" checked=checked");
								}
							}
							sb.append(" value=" + tOperation.getId() + "_" + id + ">" + tOperation.getOperationName());
							attributes.append(sb.toString());
						}
					}
				}
				tg.setOperations(attributes.toString());
			}

			treegrid.add(tg);
		}
		return treegrid;
	}

	/**
	 * 通过接口获取sql
	 *
	 * @param mapper
	 * @param methodName
	 * @param args
	 * @return
	 */
	public String getMapperSql(Object mapper, String methodName, Object... args) {
		return SqlHelper.getMapperSql(mapper, methodName, args);
	}

	public String getMapperSql(String commandId, Map<String, ?> model) {

		return "";
	}
}
